<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Poll_model extends CI_Model {

    function __construct()
    {
        // Call the Model constructor
        parent::__construct();
		$this->load->helper('date');
    }
	
	/**
	 * Key-Value pair의 2차원 배열 입력받아 INSERT문의 VALUES로 변환
	 */
	function _array_to_values($array, $default=array()){
		if(count($array) == 0)
			return NULL;
		
		$values = array();
		foreach($array as $k => $row){
			foreach($row as $l => $val){
				$row[$l] = "'".mysql_real_escape_string($val)."'";
			}
			$values[] = '('.implode(',', array_merge($default, $row)).')';
		}
		return implode(',', $values);
	}
	
	function create_common($data)
	{
		$keys = array_keys($data);
		foreach($data as $k => $i){
			$data[$k] = "'".mysql_real_escape_string($i)."'";
		}
		$sql = 'INSERT INTO `poll_common` ('.implode(',', $keys).') VALUES ('.implode(',', $data).')';
		$query = $this->db->query($sql);
		if(mysql_affected_rows() > 0){
			$query = $this->db->query('SELECT LAST_INSERT_ID() as `id`');
			$res = $query->row();
			return $res->id;
		}
		return FALSE;
	}
	
	function create_conditions($poll_id, $data)
	{
		if(count($data) == 0)
			return 0;
		
		$default = array(
			'poll_id' => "'$poll_id'",
			'address_id' => 'NULL',
			'age_range' => 'NULL',
			'gender' => 'NULL',
			'education' => 'NULL'
		);
		$keys = array_keys($default);
		$values = array();
		foreach($data as $i => $row){
			foreach($row as $k => $val){
				$row[$k] = "'".mysql_real_escape_string($val)."'";
			}
			$values[] = '('.implode(',', array_merge($default, $row)).')';
		}
		$value_str = implode(',', $values);
		
		$sql = 'INSERT INTO `poll_condition`('.implode(',', $keys).') VALUES '.$value_str;
		$query = $this->db->query($sql);
		return mysql_affected_rows();
	}
	
	function create_questions($poll_id, $data)
	{
		$default = array(
			'poll_id' => "'$poll_id'",
			'type' => 'NULL',
			'title' => 'NULL',
			'options' => '\'\''
		);
		$keys = array_keys($default);
		
		$converted_rows = array();
		foreach($data as $i => $row)
		{
			foreach($row as $k => $val)
			{
				if($k == 'options')
				{
					foreach($val as $i => $opt)
					{
						$val[$i] = mysql_real_escape_string($opt);
					}
					$row[$k] = "'".implode(CONCAT_GLUE, $val)."'";
				}
				else
				{
					$row[$k] = "'".mysql_real_escape_string($val)."'";
				}
			}
			$converted_rows[] = '('.implode(',', array_merge($default, $row)).')';
		}
		$value_str = implode(',', $converted_rows);
		$sql = 'INSERT INTO `poll_question`('.implode(',', $keys).') VALUES '.$value_str;
		$query = $this->db->query($sql);
		return mysql_affected_rows();
	}
	
	function create_vote($poll_id, $user_id, $answers)
	{
		foreach($answers as $k => $i){
			$answers[$k] = mysql_real_escape_string($i);
		}
		$answer_str = implode(CONCAT_DELIMITOR, $answers);
	
		$sql = "INSERT INTO `vote` (`poll_id`, `user_id`, `answers`) VALUES ('{$poll_id}', '{$user_id}', '{$answer_str}');";
		$query = $this->db->query($sql);
		return FALSE;	
	}
	
	function get_entire_poll_by_id($id, $expired = FALSE)
	{
		$res = $this->get_poll_common_by_id($id, $expired);
		if( ! $res)
			return FALSE;
		$res->questions = $this->get_questions_by_poll_id($id, $expired);
		if( ! $res->questions)
			return FALSE;
		$res->conditions = $this->get_conditions_by_poll_id($id, $expired);
		$res->usernames = $this->get_create_vote_user($id);
		if( ! $res->usernames)
			return FALSE;
		
		return $res;
	}
	
	function get_number_of_questions($poll_id)
	{
		$sql = "
			SELECT count(`q`.`id`) as `cnt` 
			FROM `poll_common` 
			JOIN `poll_question` AS `q` ON `q`.`poll_id` = `poll_common`.`id`
			WHERE `poll_common`.`id` = '{$poll_id}' ";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
		{
			$res = $query->row();
			return $res->cnt;
		}
		return NULL;
	}
	
	function get_poll_common_by_id($id, $expired = FALSE)
	{
		$expired_str = $expired ? '_expired' : '';
		$sql = "SELECT * FROM `poll_common{$expired_str}` WHERE `id` = '$id'";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
			return $query->row();
		return FALSE;
	}	
	
	function get_questions_by_poll_id($id, $expired = FALSE)
	{
		$expired_str = $expired ? '_expired' : '';
		$sql = "SELECT * FROM `poll_question{$expired_str}` WHERE `poll_id` = '$id'";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
		{
			$res = $query->result();
			foreach($res as $k => $row)
			{
				if($row->options != '')
				{
					$res[$k]->options = explode(CONCAT_DELIMITOR, $row->options);
				}
			}
			return $res;
		}
			
		return FALSE;
	}
	
	function get_conditions_by_poll_id($id, $expired = FALSE)
	{
		$expired_str = $expired ? '_expired' : '';
		$sql = "SELECT * FROM `poll_condition{$expired_str}` WHERE `poll_id` = '$id'";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
			return $query->result();
		return FALSE;
	}	

	function get_all_polls()
	{
		$sql = "SELECT * FROM `poll_common`";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
			return $query->result();
		return NULL;
	}
	
	function get_available_polls($lat, $lng, $start_id = 0)
	{
		$buff = date("Y-m-d H:m:s");
		$sql = "SELECT poll_common.*  
			FROM `poll_common` 
			JOIN location_gps on location_gps.address_str like CONCAT('%',poll_common.location,'%')
			WHERE 
				(	
					(poll_start < '$buff' AND poll_end > '$buff') AND 
					(type = 0 OR (type = 1 AND daily_start < '$buff' AND daily_end > '$buff'))
				)
				AND	radius > (1000 * 6371 * acos( cos( radians('$lat') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('$lng') ) + sin( radians('$lat') ) * sin( radians( lat ) ) ) )
				AND poll_common.id > '$start_id'
			GROUP BY poll_common.id
			ORDER BY id DESC 
			";
		$query = $this->db->query($sql);
		if($query->num_rows()>0) return $query->result();
		return array();
	}
	
	/**
	 * 설문과 사용자 ID를 받아서 설문에 조건 자체가 존재하지 않거나
	 * 사용자 정보가 설문의 요구사항을 만족시키는지 여부를 리턴
	 * [주의] 설문 자체가 존재하지 않아도 TRUE 리턴, 사용 전 설문 존재 여부 검사해야
	 * 
	 * @author 박지수
	 * @param int 	설문 ID
	 * @param int	사용자 ID
	 * @return bool	사용자의 조건 만족 여부
	 */
	function is_user_matched_to_conditions($poll_id, $user_id)
	{
		$sql = "
			SELECT NOT(ISNULL(u.id)) as is_matched  
			FROM poll_condition AS c
			LEFT JOIN user AS u ON 
				u.id = {$user_id} AND
				(ISNULL(c.education) 	OR c.education <= u.education	) AND
				(ISNULL(c.gender) 		OR c.gender = u.gender			) AND
				(ISNULL(c.address_id) 	OR c.address_id = u.address1_id 
										OR c.address_id = u.address2_id 
										OR c.address_id = u.address3_id	) AND
				(ISNULL(c.age_range)	OR c.age_range = TRUNCATE(TIMESTAMPDIFF(YEAR, u.birthday, now()) +1 , -1) )
			WHERE 
				c.poll_id = {$poll_id} 
			GROUP BY c.id
			ORDER BY u.id desc 
			LIMIT 1
		";
		
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
		{
			$res = $query->row();
			return $res->is_matched;
		}
		return TRUE;
	}
	
	/**
	 * 
	 */
	function is_location_matched($poll_id, $lat, $lng)
	{
		$sql = "
			SELECT l.id   
			FROM poll_common AS p
			JOIN location_gps AS l ON 
				l.address_str LIKE CONCAT('%', p.location, '%') AND
				l.radius > (1000 * 6371 * acos( cos( radians('$lat') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('$lng') ) + sin( radians('$lat') ) * sin( radians( lat ) ) ) )
			WHERE 
				p.id = {$poll_id} 
			GROUP BY p.id
			LIMIT 1
		";
		
		$query = $this->db->query($sql);
		return $query->num_rows> 0;
	}
	
	function get_latest_vote_by_user($poll_id, $user_id)
	{
		$sql = "
			SELECT timestamp FROM vote
			WHERE poll_id = '{$poll_id}' AND user_id = '{$user_id}'
			GROUP BY id
			ORDER BY id DESC
			LIMIT 1
		";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
		{
			$res = $query->row();
			return $res->timestamp;
		}
		return NULL;
	}
	
	function count_vote($poll_id, $poll_type)
	{
		$repeated_where = $poll_type === POLL_TYPE_REPEATED ? "AND `timestamp` >= CURRENT_DATE":'';
		$sql = "
			SELECT COUNT(vote.id) as cnt 
			FROM vote
			WHERE 
				poll_id = '{$poll_id}' 
				{$repeated_where}
		";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
		{
			$res = $query->row();
			return $res->cnt;
		}
		return NULL;
		
	}
		

	
	function search($data, $expired = FALSE)
	{
		$tbl_poll_common = 'poll_common'.($expired ? '_expired':'').' AS p';
		$tbl_poll_condition =  'poll_condition'.($expired ? '_expired':'').' AS c';
		
		$opt = new stdClass;
		$opt->condition = array(
			'education' => "c.education = '%s'",
			'address_id' => "c.address_id = '%s'",
			'gender' => "c.education = '%s'",
			'age_range' => "c.age_range = '%s'"
		);
		$opt->where = array(
			'location' => "p.location LIKE '%%%s%%",
			'category' => "p.category = '%s'",
			'keyword' => "p.keyword LIKE '%%%s%%'",
			'title' => "p.title LIKE '%%%s%%'",
		);
		
		$opt_str = array(
			'condition' => array(),
			'where' => array()
		);
		
		foreach($data as $k => $i){
			if(trim($i) != ''){
				foreach($opt as $type => $field){
					if(isset($field[$k])){
						$opt_str[$type][] = sprintf($field[$k], $i);  
						break;
					}
				}
			}
		}
		
		$join_str = '';
		if( ! empty($opt_str['condition']))
			$join_str = "JOIN poll_condition AS c ON ".implode(' AND ', $opt_str['condition']);
		
		$where_str = '';
		if( ! empty($opt_str['where']))
			$where_str = 'WHERE '.implode(' AND ', $opt_str['where']);
		
		if($join_str.$where_str == '')
			return FALSE;
		
		$sql = "
			SELECT p.* FROM {$tbl_poll_common}
			{$join_str} {$where_str}
			GROUP BY p.id 
			ORDER BY p.id DESC
		";
		
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
		{
			return $query->result();
		}
		return FALSE;
	}

	function high_access_polls(){
		$sql = "SELECT * FROM `poll_common` ORDER BY view_count DESC";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
			return $query->result();
		return NULL;
	}


	function get_result_votes($poll_id)
	{
		$sql = "SELECT `v`.`user_id`,`v`.`poll_id`, `answers`, `timestamp`, `birthday`, `gender`, `education`
				FROM `vote` AS `v`
				LEFT JOIN `user` AS `u` ON `v`.`user_id` = `u`.`id`
				WHERE `v`.`poll_id` = '$poll_id' ";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
		{
			$res = $query->result_array();
			foreach($res as $k => $i)
			{
				$res[$k]['answers'] = explode(CONCAT_DELIMITOR, $i['answers']);
			}
			return $res;
		}
		return NULL;		
}
	
	function get_create_vote_user($poll_id, $expired = FALSE)
	{
		$expired_str = $expired ? '_expired' : '';
		$sql = "SELECT `username`
				FROM `poll_common{$expired_str}` AS `p`
				JOIN `user` AS	`u` ON `p`.`user_id` = `u`.`id`
				WHERE `p`.`id` = '$poll_id' ";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
		{
			return $query->row();
		}
		return FALSE;

	}
	
	function delete_poll_by_id($id)
	{
		$sql = "DELETE FROM poll_common
				WHERE id = '$id'";
		$query = $this->db->query($sql);
		$sql = "DELETE FROM poll_condition
				WHERE poll_id = '$id'";
		$query = $this->db->query($sql);
		$sql = "DELETE FROM poll_question
				WHERE poll_id = '$id'";
		$query = $this->db->query($sql);
		$sql = "DELETE FROM vote
				WHERE poll_id = '$id'";
		$query = $this->db->query($sql);
	}
}
